﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows;
using System.ComponentModel;

namespace examprojekt09
{
    class DataMapper
    {
        private static DataMapper instance;
        // HUSK AT RETTE CONNECTION STRING TIL DEN PC DU SIDDER VED!!!111oneoneone
        private string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\CProjects\Eksamen\examprojekt09\examprojekt09\Database_storage.mdf;Integrated Security=True;User Instance=True";
        private DataMapper() { }

        public static DataMapper Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new DataMapper();
                }
                return instance;
            }
        }
        
        public void TestConn()
        {
            string queryString = "SELECT e_name, e_email, pid FROM employee";
            // create the data adapter 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
                DataSet dataset = new DataSet();
                DataRow myDataRow;
                SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(adapter);
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                adapter.Fill(dataset, "employee");
                
                dataset.Tables["employee"].Rows[0]["e_name"] = "Peach";
                // Inserting data into the datarow.
                myDataRow = dataset.Tables["employee"].NewRow();
                myDataRow["e_name"] = "Kogler";
                myDataRow["e_email"] = "Kogler@kogler.dk";
                myDataRow["pid"] = "200";
                dataset.Tables["employee"].Rows.Add(myDataRow);

                adapter.Update(dataset, "employee");


            }
        }
        public DataSet getAllSqlData()
        {
            string queryString = "SELECT * FROM employee; SELECT * FROM project";

            DataSet dataset;
            // create the data adapter 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
                dataset = new DataSet();
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                adapter.Fill(dataset, "t");
            }
            return dataset;
        }
        public List<Project> getProjects(DataSet dataset)
        {
            List<Project> projektList = new List<Project>();
            
            foreach (DataRow dataRow in dataset.Tables[1].Rows)
            {
                //MessageBox.Show("" + dataRow[1]);
                Project newproject = new Project((int)dataRow[0], (string)dataRow[1], (string)dataRow[2], (string)dataRow[3], (string)dataRow[4], (int)dataRow[5]);
                projektList.Add(newproject);
            }
            
            return projektList;
        }
        public List<Employee> getEmployees(DataSet dataset)
        {
            List<Employee> employeeList = new List<Employee>();

            foreach (DataRow dataRow in dataset.Tables[0].Rows)
            {
                //MessageBox.Show("" + dataRow[1]);
                Employee newemployee = new Employee((int)dataRow[0], (string)dataRow[1], (string)dataRow[2], (int)dataRow[3]);
                employeeList.Add(newemployee);
            }
            
            return employeeList;
        }
        public void updateEmployeePid(int empId, int pId, DataSet dataset)
        {
            foreach (DataRow dataRow in dataset.Tables[0].Rows)
            {
                if (empId == (int)dataRow[0])
                {
                    dataRow[3] = pId;
                    
                }
            }
        }
        public int createProject(DataSet ds, string pName, string pDetail, string startDate, string endDate, int reqEmployee)
        {
            
            DataRow row = ds.Tables[1].NewRow();
            row[1] = pName;
            row[2] = pDetail;
            row[3] = reqEmployee;
            row[4] = startDate;
            row[5] = endDate;
            ds.Tables[1].Rows.Add(row);
            return ds.Tables[1].Rows.Count;    
        }
        public int createEmployee(DataSet ds, string name, string email, int pid)
        {
            
            DataRow row = ds.Tables[0].NewRow();
            row[1] = name;
            row[2] = email;
            row[3] = pid;
            ds.Tables[0].Rows.Add(row);
            return ds.Tables[0].Rows.Count;
        }
        public void commitDataSet(DataSet ds)
        {
            string queryString = "SELECT * FROM project";
            // create the data adapter 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                
                SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(adapter);
                adapter.Update(ds.Tables[1]);
            }
            string queryString2 = "SELECT * FROM employee";
            // create the data adapter 
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataAdapter adapter2 = new SqlDataAdapter(queryString2, connection);
                adapter2.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                
                SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(adapter2);
                adapter2.Update(ds.Tables[0]);
            }
            
        }

        




    }
}
